﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using KPIS.GERP.GHRMS.DAL;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.IBLL; 
using KPIS.DBM;

using KPIS.GERP.GHRMS.DALFactory;

namespace KPIS.GERP.GHRMS.BLL
{
    public class ScoreMainBLL

    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public string GetAppraisalSequence(int apr_year, int apr_period, int emp_type_seq)
        {

            try
            {
                string sql;
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@apr_year", apr_year);
                dbManager.AddParameters(1, "@apr_period", apr_period);
                dbManager.AddParameters(2, "@emp_type_seq", emp_type_seq);
                dbManager.AddParameters(3, "@ActiveRecord", SystemInfo._ActiveRecordStatus);

                sql = " SELECT a.APR_SEQ"
                    + " FROM HRM_APR_APPRAISALS a "
                    + " WHERE a.APR_YEAR = @apr_year"
                    + " AND a.APR_PERIOD = @apr_period"
                    + " AND a.EMP_TYPE_SEQ = @emp_type_seq"
                    + " AND a.RECORD_STATUS = @ActiveRecord";

                DataSet ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                return ds.Tables[0].Rows[0]["APR_SEQ"].ToString();
            }
            catch (Exception ex)
            {
                return null;
                // throw new Exception("");
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        public DataTable LoadStdScoreDtls(Nullable<int> Sequence, Nullable<int> AppraisalSequence)
        {

            try
            {
                string sql;
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@Sequence", Sequence);
                dbManager.AddParameters(1, "@AppraisalSequence", AppraisalSequence);
                dbManager.AddParameters(2, "@ActiveRecord", SystemInfo._ActiveRecordStatus);

                sql = " SELECT d.*"
                    + " FROM HRM_APR_STD_SCORE_DTLS d"
                    //+ " INNER JOIN HRM_APR_STD_SCORES s ON d.STD_SCORE_SEQ = s.STD_SCORE_SEQ"
                    + " INNER JOIN HRM_APR_APPRAISALS a ON d.APR_SEQ = a.APR_SEQ"
                    + " WHERE d.RECORD_STATUS = @ActiveRecord"
                     + " AND a.RECORD_STATUS = @ActiveRecord"
                     + (Sequence.HasValue ? " AND d.STD_DTLS_SEQ = @Sequence" : "")
                     + (AppraisalSequence.HasValue ? " AND d.APR_SEQ = @AppraisalSequence" : "");

                DataSet ds = new DataSet();
                DataTable dt;
                
                dbManager.Open();
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                return null;
                // throw new Exception("");
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        public DataTable select(int apr_year, int apr_period, int emp_type_seq)
        {

            try
            {
                string sql;
                sql = " SELECT hrm_apr_std_scores.std_score_seq,"
                    + " hrm_apr_std_score_dtls.std_dtls_seq,"
                    + " hrm_apr_std_score_dtls.remarks,"
                    + " hrm_apr_std_score_dtls.score_max,"
                    + " hrm_apr_std_score_dtls.score_min,"
                    + " hrm_apr_std_score_dtls.score_level_name,"
                    + " hrm_apr_std_score_dtls.score_level_no"
                    + " FROM "
                    + " hrm_apr_std_score_dtls"
                    + " Inner Join hrm_apr_std_scores ON hrm_apr_std_scores.std_score_seq = hrm_apr_std_score_dtls.std_score_seq"
                    + " Inner Join hrm_apr_appraisals ON hrm_apr_std_scores.std_score_seq = hrm_apr_appraisals.std_score_seq"

                    + " WHERE"
                    + " hrm_apr_appraisals.apr_year = " + apr_year
                    + " and hrm_apr_appraisals.apr_period = " + apr_period
                    + " and hrm_apr_appraisals.emp_type_seq = " + emp_type_seq
                    + " and hrm_apr_std_score_dtls.record_status = '" + SystemInfo._ActiveRecordStatus + "'";

                //where emp_seq = " + emp_seq;
                DataSet ds;
                DataTable dt;
                ds = new DataSet();
                dbManager.Open();
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0];
                return dt;
            }
            catch (Exception ex)
            {

                return null;
                // throw new Exception("");
            }
            finally
            {
                dbManager.Dispose();
            }
        }


        public DataTable select()
        {

            try
            {
                string sql;
                sql = "select scr.std_score_seq, dtl.std_dtls_seq"
                + " dtl.score_level_no,dtl.score_level_name,dtl.score_min,dtl.score_max"
                + " from hrm_apr_std_scores scr,hrm_apr_std_score_dtls dtl "
                + " where scr.std_score_seq = dtl.std_score_seq "
                    + " and scr.record_status='U'"
                    + " and dtl.record_status='U'";

                DataSet ds;
                DataTable dt;
                ds = new DataSet();
                dbManager.Open();
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0];
                return dt;
            }
            catch (Exception ex)
            {

                return null;

            }
            finally
            {
                dbManager.Dispose();  
            }
        }


        public DataTable select(int apr_year, int apr_period, int emp_type_seq,string blank)
        {
            try
            {
                string sql;
                sql = " SELECT "
                    + " hrm_apr_std_scores.std_score_seq,"
                    + " hrm_apr_std_score_dtls.std_dtls_seq,"
                    + " hrm_apr_std_score_dtls.remarks,"
                    + " hrm_apr_std_score_dtls.score_max,"
                    + " hrm_apr_std_score_dtls.score_min,"
                    + " hrm_apr_std_score_dtls.score_level_name,"
                    + " hrm_apr_std_score_dtls.score_level_no"
                    + " FROM "
                    + " hrm_apr_std_score_dtls"
                    + " Inner Join hrm_apr_std_scores ON hrm_apr_std_scores.std_score_seq = hrm_apr_std_score_dtls.std_score_seq"
                    + " Inner Join hrm_apr_appraisals ON hrm_apr_std_scores.std_score_seq = hrm_apr_appraisals.std_score_seq"

                    + " WHERE"
                    + " hrm_apr_appraisals.apr_year = " + apr_year
                    + " and hrm_apr_appraisals.apr_period = " + apr_period
                    + " and hrm_apr_appraisals.emp_type_seq = " + emp_type_seq
                    + " and hrm_apr_appraisals.record_status = '" + SystemInfo._ActiveRecordStatus + "'";

                //where emp_seq = " + emp_seq;
                DataSet ds;
                DataTable dt;
                ds = new DataSet();
                dbManager.Open();
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0];
                return dt;
            }
            catch (Exception ex)
            {

                return null;
                // throw new Exception("");
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        public void Insert(int AprSequence, decimal ScoreMin, decimal ScoreMax, string ScoreLevelName
            , int ScoreLevelNo, string Remarks, int CreatedBy)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(9);
                dbManager.AddParameters(0, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(1, "@score_min", ScoreMin);
                dbManager.AddParameters(2, "@score_max", ScoreMax);
                dbManager.AddParameters(3, "@score_level_name", ScoreLevelName);
                dbManager.AddParameters(4, "@score_level_no", ScoreLevelNo);
                dbManager.AddParameters(5, "@remarks", Remarks);
                dbManager.AddParameters(6, "@apr_seq", AprSequence);
                dbManager.AddParameters(7, "@created_by", CreatedBy);
                dbManager.AddParameters(8, "@created_when", DateTime.Today);

                dbManager.ExecuteNonQuery(CommandType.Text
                    , "INSERT INTO HRM_APR_STD_SCORE_DTLS (APR_SEQ, SCORE_MAX, SCORE_MIN, SCORE_LEVEL_NAME"
                            + ", SCORE_LEVEL_NO, REMARKS, RECORD_STATUS,CREATED_BY, CREATED_WHEN) "
                        + " VALUES(@apr_seq, @score_max, @score_min, @score_level_name"
                            + ", @score_level_no, @remarks, @record_status, @created_by, @created_when)");
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }
        public void Update(int Sequence, decimal ScoreMin, decimal ScoreMax, string ScoreLevelName
            , int ScoreLevelNo, string Remarks, int UpdateBy)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(8);
                dbManager.AddParameters(0, "@sequence", Sequence);
                dbManager.AddParameters(1, "@score_min", ScoreMin);
                dbManager.AddParameters(2, "@score_max", ScoreMax);
                dbManager.AddParameters(3, "@score_level_name", ScoreLevelName);
                dbManager.AddParameters(4, "@score_level_no", ScoreLevelNo);
                dbManager.AddParameters(5, "@remarks", Remarks);
                dbManager.AddParameters(6, "@updated_by", UpdateBy);
                dbManager.AddParameters(7, "@updated_when", DateTime.Today);
                
                dbManager.ExecuteNonQuery(CommandType.Text
                    , "UPDATE HRM_APR_STD_SCORE_DTLS "
                        + " SET SCORE_MAX = @score_max"
                        + ", SCORE_MIN = @score_min"
                        + ", SCORE_LEVEL_NAME = @score_level_name"
                        + ", SCORE_LEVEL_NO = @score_level_no"
                        + ", REMARKS = @remarks"
                        + ", UPDATED_BY=@updated_by"
                        + ", UPDATED_WHEN=@updated_when "
                    + " WHERE STD_DTLS_SEQ=@sequence");
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }
        public void Delete(int Sequence, int UpdateBy)
        {
            try
            {
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@sequence", Sequence);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@updated_by", UpdateBy);
                dbManager.AddParameters(3, "@updated_when", DateTime.Today);
                dbManager.Open();
                dbManager.ExecuteNonQuery(CommandType.Text
                    , "UPDATE HRM_APR_STD_SCORE_DTLS "
                    + "SET RECORD_STATUS = @record_status"
                    + ", UPDATED_BY = @updated_by"
                    + ", UPDATED_WHEN = @updated_when"
                    + " WHERE STD_DTLS_SEQ = @sequence");
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }

    }
}
